# -*- coding:utf-8 -*-

# 指定中文编码

######################################################################################
#                                                                                    #
#   读取csv文件生成json数据                                                             #
#                                                                                    #
######################################################################################

import xlrd
import xlwt

# Python 判断字符串是否为数字
# https://www.runoob.com/python3/python3-check-is-number.html
def is_number(s):
    try:
        float(s)
        return True
    except ValueError:
        pass

    try:
        import unicodedata

        unicodedata.numeric(s)
        return True
    except (TypeError, ValueError):
        pass

    return False


def is_float(s):
    return s.count(".") == 1


def to_value(s):
    # 转为字符串,并去掉尾部多余的0
    # https://stackoverflow.com/questions/2440692/formatting-floats-without-trailing-zeros
    strip_zero = lambda x: ("%f" % x).rstrip("0").rstrip(".")
    if is_number(s):
        ss = strip_zero(float(s))
        if is_float(ss):
            return float(ss)
        else:
            return int(ss)
    elif s == "":
        return None
    else:
        return s


def get_dict_value(obj, key, value):
    if key in obj:
        return obj[key]
    else:
        return value


def read_sheet_data(file_contents, sheet_name):
    # 打开excel文件读取数据
    exce = xlrd.open_workbook(file_contents=file_contents)
    # 获取所有sheet名称
    # print('所有sheet名称', exce.sheet_names())
    sheet1 = exce.sheet_by_name(sheet_name)  # 通过sheet名称获取
    keys = None
    datas = []
    ssk = lambda x: str(x).strip()
    ssv = lambda x: to_value(str(x).strip())
    for i in range(sheet1.nrows):
        if i == 0:
            keys = [ssk(sheet1.cell(i, j).value) for j in range(sheet1.ncols)]
        elif keys != None:
            # print([sheet1.cell(i,j).value for j in range(sheet1.ncols)])
            # print([sheet1.cell(i,j).ctype for j in range(sheet1.ncols)])
            values = [ssv(sheet1.cell(i, j).value) for j in range(sheet1.ncols)]
            datas.append(dict(zip(keys, values)))
    return keys, datas


def get_sheet_names(file_contents):
    # 打开excel文件读取数据
    exce = xlrd.open_workbook(file_contents=file_contents)
    # 获取所有sheet名称
    return exce.sheet_names()


# def read_sheet_params(file_contents, sheet_name):
#     # 打开excel文件读取数据
#     exce = xlrd.open_workbook(file_contents=file_contents)
#     sheet1 = exce.sheet_by_name(sheet_name)  # 通过sheet名称获取
#     keys = None
#     datas = None
#     ssk = lambda x: str(x).strip()
#     ssv = lambda x: to_value(str(x).strip())
#     for j in range(sheet1.ncols):
#         if j == 0:
#             keys = [ssk(sheet1.cell(i, j).value) for i in range(sheet1.nrows)]
#         elif keys != None:
#             values = [ssv(sheet1.cell(i, j).value) for i in range(sheet1.nrows)]
#             datas = dict(zip(keys, values))
#     return datas


# excel的子表名称翻译表
NAME_DICT = {
    "巷道": "edges",
    "分支": "edges",
    "节点": "nodes",
    "风扇": "fans",
    "通风机": "fans",
    "风机": "fans",
    "风门": "structs",
    "风窗": "structs",
    "构筑物": "structs",
    "配置参数": "config",
    "配置": "config",
    "参数": "config",
    # 增加其它的名称映射
    "edge": "edges",
    "tunnel": "edges",
    "tunnels": "edges",
    "node": "nodes",
    "vertex": "nodes",
    "vertexs": "nodes",
    "vertexes": "nodes",
    "fan": "fans",
    "gate": "structs",
    "gates": "structs",
    "struct": "structs",
    "option": "config",
    "options": "config",
    "configs": "config",
}

"""
参数:
    json_from_client -- 客户端json格式,
        {
            config:{
                iterN: 1000,
                minQ: 0.001,
                minH: 0.001
            },
            edges: [
                { id:1, s:1, t:1, r:0.1, qfixed:10 },
                ...
            ],
            nodes: null,
            fans: [
                { id:1, eid:3, a0:1000, a1:3.4, a0:-0.001 },
                ...
            ]
        }
返回值:
        {
            iterN: 1000,
            minQ: 0.001,
            minH: 0.001
            edges: [
                { id:1, s:1, t:1, r:0.1, qfixed: 10 },
                ...
            ],
            nodes: null,
            fans: [
                { eid:3, a0:1000, a1:3.4, a0:-0.001 },
                ...
            ]            
        }
"""


def get_xls_file_contents(xls_file):
    with open(xls_file, "rb") as f:
        return f.read()


# 两列数据(name, value)视为属性数据
def is_property_data(keys):
    if len(keys) != 2:
        return False
    name_field = get_dict_value(NAME_DICT, keys[0], keys[0])
    value_field = get_dict_value(NAME_DICT, keys[1], keys[1])
    return name_field == "name" and value_field == "value"


# 读取分支、风机xlsx文件
# 如果file_contents参数为不为None,则忽略xls_file参数
def xls_file_to_json(xls_file=None, file_contents=None, name_dict={}):
    _file_contents = file_contents
    if _file_contents == None and xls_file != None:
        _file_contents = get_xls_file_contents(xls_file)
    if _file_contents == None:
        return {}

    # 合并翻译词典
    NAME_DICT.update(name_dict)

    sheet_names = get_sheet_names(_file_contents)
    # print(sheet_names)
    json_datas = {}
    for name in sheet_names:
        # 子表名翻译成json数据格式中规定的字段名
        _name = get_dict_value(NAME_DICT, name, name)
        # 读取分支、风机、节点、构筑物表的数据
        keys, datas = read_sheet_data(_file_contents, name)
        if is_property_data(keys):
            # 特殊处理, 这是一份dict数据
            json_datas[_name] = dict([(obj["name"], obj["value"]) for obj in datas])
        else:
            json_datas[_name] = datas
    # print(json_datas)
    return json_datas


# 从dict中提取数据
# {a:1, b:2, c:3}
def get_fields_from_object(obj):
    return [name for name in obj]


# 从list中提取字段
# [ {a:1, b:2}, {a:1, b:2, c:3}, {a:1, c:6} ]
def get_fields_from_list(arr):
    field_set = set()
    for obj in arr:
        if type(obj) == dict:
            field_set.update(get_fields_from_object(obj))
    return list(field_set)


def value_to_simple_type(value):
    # 尝试转换成xls能识别的数据类型(int/float/string)
    try:
        # 数据类型
        _type = type(value)
        # 临时变量
        _value = None
        if _type == list:
            # 先将list转换为dict,再转换为json字符串
            _value = json.dumps(dict(enumerate(value)), ensure_ascii=False)
        elif _type == dict:
            # 将dict转换为json对象
            _value = json.dumps(value, ensure_ascii=False)
        elif _type == bool:
            # 布尔类型转换为0和1
            _value = 1 if value else 0
        elif _type == int or _type == float or _type == str:
            # 基本类型不进行转换
            _value = value
        else:
            # 其它数据类型尝试使用json进行转换
            _value = json.dumps(value, ensure_ascii=False)
            if _value == "null":
                _value = None
    except:
        _value = None
    return _value


def write_obj_to_sheet(worksheet, obj, fields):
    # 写入标题(第1行, 共2列)
    worksheet.write(0, 0, "name")
    worksheet.write(0, 1, "value")
    for j, key in enumerate(fields):
        # 写入key(第1列)
        worksheet.write(j + 1, 0, str(key))
        # 写入数据(第2列)
        value = value_to_simple_type(get_dict_value(obj, key, None))
        if value is not None:
            worksheet.write(j + 1, 1, value)


def write_list_to_sheet(worksheet, arr, fields):
    # 写入标题(第1行)
    for j, key in enumerate(fields):
        worksheet.write(0, j, key)
    line = 1
    for obj in arr:
        if type(obj) == dict:
            for j, key in enumerate(fields):
                value = value_to_simple_type(get_dict_value(obj, key, None))
                if value is not None:
                    worksheet.write(line, j, value)
            line += 1


# json对象写入excel表格
# 参数:
#   json_from_client -- 词典对象
#   xls_file         -- xls文件路径
def json_to_xls_file(json_from_client, xls_file):
    # 创建一个workbook 设置编码
    workbook = xlwt.Workbook(encoding="utf-8")
    for name in json_from_client:
        value = json_from_client[name]
        if type(value) == list:
            # value是list
            worksheet = workbook.add_sheet(name)
            write_list_to_sheet(worksheet, value, get_fields_from_list(value))
        elif type(value) == dict:
            # value是dict
            worksheet = workbook.add_sheet(name)
            write_obj_to_sheet(worksheet, value, get_fields_from_object(value))
    # 保存
    workbook.save(xls_file)
